package com.moss.cloud.plugins.biz.utils;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.moss.cloud.common.core.exception.BaseException;
import com.moss.cloud.common.core.exception.SystemErrorType;
import com.moss.cloud.plugins.api.model.dto.CustomDataSource;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.Objects;


/**
 * sql 执行器
 *
 * @author 宋磊
 * @date 2023年12月8日
 */
@Slf4j
public class SqlActuator {
    /**
     * 获取数据库连接
     *
     * @param dataSource
     * @return
     * @throws SQLException
     */
    private static Connection getConnection(CustomDataSource dataSource) throws SQLException {
        return DriverManager.getConnection(dataSource.getUrl(), dataSource.getUserName(), dataSource.getPassword());
    }

    /**
     * 创建表
     *
     * @param dataSource
     * @param sql
     */
    public static int createTable(CustomDataSource dataSource, String sql) {
        try (Connection connection = getConnection(dataSource);
             Statement statement = connection.createStatement()) {
            int result = statement.executeUpdate(sql);
            log.info("Table created successfully.{}", result);
            closeResource(connection, statement, null);
            return result;
        } catch (SQLException e) {
            throw new BaseException(SystemErrorType.SYSTEM_ERROR,e.getMessage());
        }
    }

    public static JSONArray executeQuery(CustomDataSource dataSource, String query) {
        log.info("query sql:{}",query);
        try (Connection connection = getConnection(dataSource);
             Statement statement = connection.createStatement()) {
            // 执行查询
            ResultSet resultSet = statement.executeQuery(query);
            JSONArray array = getResult(resultSet);
            // 关闭连接和资源
            closeResource(connection, statement, resultSet);
            log.info("query successfully.{}", array);
            return array;
        } catch (SQLException e) {
            throw new BaseException(SystemErrorType.SYSTEM_ERROR,e.getMessage());
        }
    }

    private static JSONArray getResult(ResultSet resultSet) throws SQLException {
        // 创建 JSONArray 对象
        JSONArray array = new JSONArray();
        // 获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        // 遍历结果集
        while (resultSet.next()) {
            JSONObject object = getRowData(metaData, columnCount, resultSet);
            array.add(object);
        }
        return array;
    }

    private static JSONObject getRowData(ResultSetMetaData metaData, int columnCount, ResultSet resultSet) throws SQLException {
        // 创建 JsonObject 用于存储每一行的数据
        JSONObject object = new JSONObject();
        // 动态获取每一列的数据
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            Object columnValue = resultSet.getObject(i);
            // 将数据添加到 JsonObject
            if (Objects.isNull(columnValue)) {
                object.put(columnName, "");
            } else {
                object.put(columnName, columnValue.toString());
            }
        }
        return object;
    }

    private static void closeResource(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet != null) resultSet.close();
        statement.close();
        connection.close();
    }

    public static boolean executeRemove(CustomDataSource dataSource, String sql) {
        try (Connection connection = getConnection(dataSource);
             Statement statement = connection.createStatement()) {
            // 执行删除表的语句
            boolean flag = statement.execute(sql);
            log.info("del table successfully.{}", sql);
            closeResource(connection, statement, null);
            return flag;
        } catch (SQLException e) {
            throw new BaseException(SystemErrorType.SYSTEM_ERROR,e.getMessage());
        }
    }

//    public static int insert(CustomDataSource dataSource, List<TableFieldValues> fields, String tableName) {
//        try (Connection connection = getConnection(dataSource)) {
//            String sql = "INSERT INTO " + tableName + " SET " + tableValues(fields) + defaultValue();
//            log.info("insert sql:{}", sql);
//            // 创建 PreparedStatement 对象
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            // 执行插入语句
//            int rowsAffected = preparedStatement.executeUpdate();
//            // 关闭连接
//            preparedStatement.close();
//            connection.close();
//            log.info("add table successfully.{}", rowsAffected);
//            return rowsAffected;
//        } catch (SQLException e) {
//            throw new BaseException(e.getMessage());
//        }
//    }

//    private static String tableValues(List<TableFieldValues> fields) {
//        StringBuffer sb = new StringBuffer();
//        if (CollectionUtils.isNotEmpty(fields)) {
//            fields.forEach(item -> {
//                sb.append(item.getField()).append("=").append(item.getDataType().equals(VARCHAR.getCode()) ? "'" + item.getValue() + "'" : item.getValue()).append(",");
//            });
//        }
//        return sb.toString();
//    }
//
//    private static String defaultValue() {
//        return "id=" + GenUtil.nextStrId() + "," + "creator_id='system',creator_name='system'," +
//                "modifier_id='system',modifier_name='system',create_time=" + "'" + LocalDateTime.now() + "'" +
//                ",update_time=" + "'" + LocalDateTime.now() + "'" + ",deleted=0";
//    }

//    public static String assembleQuerySql(String tableName, QueryResultParam param) {
//        String sql = "SELECT * FROM " + tableName + " WHERE 1=1";
//        if (StringUtils.isNotBlank(param.getArea_code())) {
//            sql = sql + " AND area_code=" + "'" + param.getArea_code() + "'";
//        }
//        if (StringUtils.isNotBlank(param.getDate_time())) {
//            sql = sql + " AND date_time=" + "'" + param.getDate_time() + "'";
//        }
//        if (StringUtils.isNotBlank(param.getDate_type())) {
//            sql = sql + " AND date_type=" + "'" + param.getDate_type() + "'";
//        }
//        return sql;
//    }

    /**
     * 示例
     *
     * @param args
     */
    public static void main(String[] args) throws SQLException {
//        CustomDataSource dataSource = CustomDataSource.builder().url("jdbc:mysql://192.168.3.34:3306/wn-reform").userName("root")
//                .password("ipicom123").build();
//        JSONArray array = executeQuery(dataSource, assembleQuerySql("gross_product",null));
//        System.out.println(array);
    }
}
